

use PERMNO date RET PRC VOL DLRET DLSTCD BID ASK using "$jfqa_rep/crsp_daily_all.dta", clear

qui{
rename PERMNO permno
rename RET ret
rename PRC prc 
rename VOL vol
rename DLRET dlret
rename DLSTCD dlstcd 
rename BID bid 
rename ASK ask 

*define dates
gen month=month(date)
gen year=year(date)

*delete duplicates and clean prc, ret
sort permno date
drop if date==date[_n-1] & permno==permno[_n-1]
replace prc=abs(prc)

*de-listed returns
replace ret=dlret if missing(ret)
replace ret=-0.3 if missing(ret) & dlstcd==500
replace ret=-0.3 if missing(ret) & dlstcd==520
replace ret=-0.3 if missing(ret) & dlstcd>550 & dlstcd<575
replace ret=-0.3 if missing(ret) & dlstcd==580
replace ret=-0.3 if missing(ret) & dlstcd==584
replace ret=-1 if missing(ret) & ~missing(dlstcd)
drop dlstcd dlret

gen dollar_vol=vol*prc
gen spread=(ask-bid)/prc

collapse spread (rawsum) dollar_vol, by(permno year month)

}

save "$jfqa_rep/14_Spread_Volume.dta", replace
